<?php
require_once(getcwd().'/application/models/SS_model.php');

class Leave_model extends SS_model
{
	public function __construct()
	{
		parent::__construct();
		$this->load->database();
		$this->load->library('session');
		$this->load->library('encryption');
	}

	public function getTotalLeaveDays()
	{
		$sql = 'SELECT DATE_FORMAT(start_time,"%m") as month ,COUNT(leave_apply.auto_id) as num FROM leave_apply WHERE DATE_FORMAT(start_time,"%Y") = ? AND applier_id = ? AND leave_type = "leave" GROUP BY DATE_FORMAT(start_time,"%m");';

		$data =  $this->db->query($sql,array(date("Y"),$_SESSION['user_id']))->result_array();
		$res = array();
		for($i=0;$i<12;$i++)
		{
			$res[$i] = 0;
		}
		foreach($data as $r=>$v)
		{
			$res[$v['month']-1] = $v['num'];
		}
		return $res;
		//var_dump($res) ;exit;
	}

	public function getTotalSickDays()
	{
		$sql = 'SELECT DATE_FORMAT(start_time,"%m") as month ,COUNT(leave_apply.auto_id) as num FROM leave_apply WHERE DATE_FORMAT(start_time,"%Y") = ? AND applier_id = ? AND leave_type = "sick_leave" GROUP BY DATE_FORMAT(start_time,"%m");';

		$data =  $this->db->query($sql,array(date("Y"),$_SESSION['user_id']))->result_array();
		// var_dump($data);exit;
		$res = array();
		for($i=1;$i<=12;$i++)
		{
			$res[$i] = 0;
		}
		foreach($data as $r=>$v)
		{
			$res[(int)$v['month']] = $v['num'];
			//parse_int
		}
		return $res;
	}

	public function getLeaveByMonthState($date)
	{
		$sql = 'SELECT `auto_id`,  DATE_FORMAT(`create_time`,"%Y-%m-%d %H:%i") as create_time, DATE_FORMAT(`start_time`,"%Y-%m-%d %H:%i") as start_time, DATE_FORMAT(`end_time`,"%Y-%m-%d %H:%i") as end_time, `time_length`,  `state` FROM `leave_apply` WHERE applier_id = ? AND DATE_FORMAT(start_time,"%Y-%m") = ? AND leave_type = ? ORDER BY start_time DESC ;';
		return $this->db->query($sql,array($_SESSION['user_id'],$date,"leave"))->result_array();
	}

	public function getSickLeaveByMonthState($date)
	{
		$sql = 'SELECT `auto_id`,  DATE_FORMAT(`create_time`,"%Y-%m-%d %H:%i") as create_time, DATE_FORMAT(`start_time`,"%Y-%m-%d %H:%i") as start_time, DATE_FORMAT(`end_time`,"%Y-%m-%d %H:%i") as end_time, `time_length`,  `state` FROM `leave_apply` WHERE applier_id = ? AND DATE_FORMAT(start_time,"%Y-%m") = ? AND leave_type = ? ORDER BY start_time DESC ;';
		return $this->db->query($sql,array($_SESSION['user_id'],$date,"sick_leave"))->result_array();
	}

	public function getYears()
	{
		$sql = 'SELECT DISTINCT(DATE_FORMAT(start_time,"%Y")) as Year FROM leave_apply WHERE 1;';
		return $this->db->query($sql)->result_array();
	}

	public function insertLeaveApply($data)
	{
		return $this->_redisKeyInterFace("_insertLeaveApply","_insertLeaveApply",$data);
	}

	public function _insertLeaveApply($data)
	{
		if($data['leave_type'] != "leave" && $data['leave_type'] != 'sick_leave')
		{
			return array("code"=>"102","text"=>"wrong leave type");
		}
		if(strtotime($data['start_time']) > strtotime($data['end_time']) || empty($data['end_time']) || empty($data['start_time']) || empty($data['time_length']) || empty($data['reason']) )
		{
			return array("code"=>"102","text"=>"Please enter Leave Time in correct format");
		}

		//Insert new Leave
		$data['reason'] = trim($data['reason']);
		$sql = "INSERT INTO `leave_apply`(`auto_id`, `applier_id`, `create_time`, `start_time`, `end_time`, `time_length`, `hr_id`, `leave_type`, `reason`, `state`) VALUES (null,?,?,?,?,?,?,?,?,?) ;";
		$this->db->query($sql,array($_SESSION['user_id'],date("Y-m-d H:i:s"),$data['start_time'],$data['end_time'],$data['time_length'],0,$data['leave_type'],$data['reason'],"pending"));

		//Insert leave  log 
		$sql = "INSERT INTO `leave_related_log`(`auto_id`, `user_id`, `methods`, `create_time`, `leave_balance_change`, `sick_balance_change`) VALUES (null,?,?,?,?,?) ;";
		
		$this->db->query($sql ,array($_SESSION['user_id'],"pend-leave",date("Y-m-d H:i:s"),0,0));

		//Mail to Manager
		$data = $this->getNameAndMgrMail($_SESSION['user_id']);
		$data['title'] = "You've received a new leave request";
		$data['content'] = "You've received a new leave request from ".$data['user_name']."\nPlease log in and check .\nhttp://alexfu.cc/approve;";
		$this->mail_to($data);


		return array("code"=>"200");
	}

	public function _updateApplyCancel($data)
	{
		if($data['leave_type'] != "leave" && $data['leave_type'] != 'sick_leave')
		{
			return array("code"=>"102","text"=>"wrong leave type");
		}
		$sql = 'SELECT auto_id FROM leave_apply WHERE auto_id = ? AND applier_id = ? AND state = ? ;';
		$res = $this->db->query($sql,array($data['id'],$_SESSION['user_id'],"pending"))->row_array();
		if(isset($res['auto_id']))
		{
			$sql = "UPDATE leave_apply SET state = 'cancelld' WHERE auto_id = ? ;";
			$this->db->query($sql,array($data['id']));

			$sql = "INSERT INTO `leave_related_log`(`auto_id`, `user_id`, `methods`, `create_time`, `leave_balance_change`, `sick_balance_change`) VALUES (null,?,?,?,?,?) ;";

			$this->db->query($sql ,array($_SESSION['user_id'],"cancel-".$data['leave_type'],date("Y-m-d H:i:s"),0,0));



			return array("code"=>"200");
		}
		return array("code"=>"102","text"=>"Cancel Operation has been denyed,While the manager/hr has approved your request  OR it is on cancelld state");
	}
	public function updateApplyCancel($data)
	{
		return $this->_redisKeyInterFace("_updateApplyCancel","_updateApplyCancel",$data);
	}

	public function pendState($data)
	{
		if($data['leave_type'] != "leave" && $data['leave_type'] != 'sick_leave')
		{
			return array("code"=>"102","text"=>"wrong leave type");
		}
		$sql = 'SELECT auto_id FROM leave_apply WHERE auto_id = ? AND applier_id = ? AND state = ? ;';
		$res = $this->db->query($sql,array($data['id'],$_SESSION['user_id'],"cancelld"))->row_array();
		if(isset($res['auto_id']))
		{
			$sql = "UPDATE leave_apply SET state = 'pending' WHERE auto_id = ? ;";
			$this->db->query($sql,array($data['id']));

			$sql = "INSERT INTO `leave_related_log`(`auto_id`, `user_id`, `methods`, `create_time`, `leave_balance_change`, `sick_balance_change`) VALUES (null,?,?,?,?,?) ;";

			$this->db->query($sql ,array($_SESSION['user_id'],"re-pend-".$data['leave_type'],date("Y-m-d H:i:s"),0,0));



			return array("code"=>"200");
		}
		return array("code"=>"102","text"=>"Pending Operation has been denyed,While the manager/hr has approved your request OR it is on pending state");
	
	}

	public function detailState($id)
	{
		$sql = "SELECT * FROM leave_apply WHERE auto_id = ? AND applier_id = ? ;";
		return $this->db->query($sql,array($id,$_SESSION['user_id']))->row_array();
	}

	public function editState($data)
	{
		if($data['leave_type'] != "leave" && $data['leave_type'] != 'sick_leave')
		{
			return array("code"=>"102","text"=>"wrong leave type");
		}
		$sql = 'SELECT auto_id FROM leave_apply WHERE auto_id = ? AND applier_id = ? AND state = ? OR state = ?  ;';
		$res = $this->db->query($sql,array($data['id'],$_SESSION['user_id'],"pending","cancelld"))->row_array();
		if(isset($res['auto_id']))
		{
			$sql = "UPDATE leave_apply SET start_time = ? ,end_time = ? ,time_length = ?,reason = ?  WHERE auto_id = ? ;";
			$this->db->query($sql,array($data['start_time'],$data['end_time'],$data['time_length'],$data['reason'],$data['id']));

			$sql = "INSERT INTO `leave_related_log`(`auto_id`, `user_id`, `methods`, `create_time`, `leave_balance_change`, `sick_balance_change`) VALUES (null,?,?,?,?,?) ;";

			$this->db->query($sql ,array($_SESSION['user_id'],"edit-".$data['leave_type'],date("Y-m-d H:i:s"),0,0));
			return array("code"=>"200");
		}
		return array("code"=>"102","text"=>"Edit Operation has been denyed,While the manager/hr has approved your request!");
	}

	public function getNameAndMgrMail($id)
	{
		$sql = "SELECT mine.user_name ,mgr.user_id as `to` FROM profile as mine LEFT OUTER JOIN leader ON mine.user_id = leader.user_id LEFT OUTER JOIN users as mgr ON leader.leader_id = mgr.auto_id WHERE mine.user_id = ?;";
		return $this->db->query($sql,array($id))->row_array();
	}
}